{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "D:\\ProgramData\\Anaconda3\\lib\\site-packages\\ipykernel\\__main__.py:65: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>EID</th>\n",
       "      <th>RGYEAR</th>\n",
       "      <th>HY</th>\n",
       "      <th>ZCZB</th>\n",
       "      <th>ETYPE</th>\n",
       "      <th>MPNUM</th>\n",
       "      <th>INUM</th>\n",
       "      <th>FINZB</th>\n",
       "      <th>FSTINUM</th>\n",
       "      <th>TZINUM</th>\n",
       "      <th>...</th>\n",
       "      <th>inv_ZP03</th>\n",
       "      <th>inv_recruit_tot</th>\n",
       "      <th>inv_re_month</th>\n",
       "      <th>inv_ZB_pro</th>\n",
       "      <th>inv_dificit</th>\n",
       "      <th>inv_end_if</th>\n",
       "      <th>inv_endyear_impor</th>\n",
       "      <th>inv_dif_wei</th>\n",
       "      <th>invest_if</th>\n",
       "      <th>TARGET</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>32741</td>\n",
       "      <td>2000</td>\n",
       "      <td>87</td>\n",
       "      <td>100.0</td>\n",
       "      <td>7</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>-20.0</td>\n",
       "      <td>250.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.168155</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>294220</td>\n",
       "      <td>2003</td>\n",
       "      <td>51</td>\n",
       "      <td>50.0</td>\n",
       "      <td>7</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10526</td>\n",
       "      <td>2013</td>\n",
       "      <td>75</td>\n",
       "      <td>100.0</td>\n",
       "      <td>7</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>237382</td>\n",
       "      <td>2014</td>\n",
       "      <td>75</td>\n",
       "      <td>9900.0</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>473535</td>\n",
       "      <td>2008</td>\n",
       "      <td>75</td>\n",
       "      <td>50.0</td>\n",
       "      <td>7</td>\n",
       "      <td>3.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 194 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "      EID  RGYEAR  HY    ZCZB  ETYPE  MPNUM  INUM  FINZB  FSTINUM  TZINUM  \\\n",
       "0   32741    2000  87   100.0      7    1.0   3.0    0.0      2.0     0.0   \n",
       "1  294220    2003  51    50.0      7    0.0   3.0    0.0      0.0     0.0   \n",
       "2   10526    2013  75   100.0      7    1.0   2.0    0.0      1.0     0.0   \n",
       "3  237382    2014  75  9900.0      7    3.0   4.0    0.0      2.0     0.0   \n",
       "4  473535    2008  75    50.0      7    3.0   5.0    0.0      1.0     0.0   \n",
       "\n",
       "    ...    inv_ZP03  inv_recruit_tot  inv_re_month  inv_ZB_pro  inv_dificit  \\\n",
       "0   ...         0.0              0.0         -20.0       250.0          0.0   \n",
       "1   ...         0.0              0.0           0.0         0.0          0.0   \n",
       "2   ...         0.0              0.0           0.0         0.0          0.0   \n",
       "3   ...         0.0              0.0           0.0         0.0          0.0   \n",
       "4   ...         0.0              0.0           0.0         0.0          0.0   \n",
       "\n",
       "   inv_end_if  inv_endyear_impor  inv_dif_wei  invest_if  TARGET  \n",
       "0         0.0           0.168155          0.0          1     0.0  \n",
       "1         0.0           0.000000          0.0          0     0.0  \n",
       "2         0.0           0.000000          0.0          0     0.0  \n",
       "3         0.0           0.000000          0.0          0     1.0  \n",
       "4         0.0           0.000000          0.0          0     1.0  \n",
       "\n",
       "[5 rows x 194 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import os\n",
    "import datetime\n",
    "from sklearn import preprocessing\n",
    "os.chdir(\"C:/Users/Ma/Desktop/document/企业经营退出风险预测/analysis\")\n",
    "\n",
    "# data = pd.read_csv('data.csv',encoding='gb2312')\n",
    "entbase = pd.read_csv('1entbase.csv',encoding='gb2312')\n",
    "alter = pd.read_csv('2alter.csv',encoding='utf8')\n",
    "branch = pd.read_csv('3branch.csv',encoding='gb2312')\n",
    "invest = pd.read_csv('4invest.csv',encoding='gb2312')\n",
    "right = pd.read_csv('5right.csv',encoding='gb2312')\n",
    "project = pd.read_csv('6project.csv',encoding='gb2312')\n",
    "lawsuit = pd.read_csv('7lawsuit.csv',encoding='gb2312')\n",
    "breakfaith = pd.read_csv('8breakfaith.csv',encoding='gb2312')\n",
    "recruit = pd.read_csv('9recruit.csv',encoding='gb2312')\n",
    "\n",
    "# 注册资本缺失值填充\n",
    "# scaler = preprocessing.MinMaxScaler()\n",
    "entbase['ZCZB']=entbase['ZCZB'].groupby([entbase.ETYPE,entbase.HY]).apply(lambda g:g.fillna(g.mean()))\n",
    "entbase['MPNUM'] = entbase['MPNUM'].fillna(0)\n",
    "# entbase['MPNUM'] = scaler.fit_transform(entbase.MPNUM)\n",
    "entbase['INUM'] = entbase['INUM'].fillna(0)\n",
    "entbase['FINZB'] = entbase['FINZB'].fillna(0)\n",
    "entbase['FSTINUM'] = entbase['FSTINUM'].fillna(0)\n",
    "entbase['TZINUM'] = entbase['TZINUM'].fillna(0)\n",
    "entbase['num'] = entbase['MPNUM']+ entbase['INUM']+entbase['FINZB']+entbase['FSTINUM']+entbase['FSTINUM']+entbase['TZINUM']\n",
    "entbase['hyty'] = (entbase.HY.astype('str') + entbase.ETYPE.astype('str')).astype('int')\n",
    "entbase['year_imp']= 1/(entbase['RGYEAR'] - entbase.RGYEAR.min()+1)\n",
    "entbase['id_imp']= 1/(entbase['EID'] - entbase.EID.min()+1)\n",
    "entbase['old'] = 2016 - entbase.RGYEAR\n",
    "# entbase['ZB'] = entbase.ZCZB + entbase.FINZB\n",
    "data = entbase\n",
    "\n",
    "alter = pd.read_csv('2alter.csv',encoding='utf8')\n",
    "# 去重，有很多重复的\n",
    "alter.drop_duplicates(inplace=True)\n",
    "\n",
    "# 去除字符中的万元和小数点\n",
    "alter.ALTBE = alter.ALTBE.astype('str')\n",
    "alter.ALTBE = alter.ALTBE.apply(lambda x :x.strip('万元').split('.')[0] if ~pd.isnull(x) else x)\n",
    "alter.ALTAF = alter.ALTAF.astype('str')\n",
    "alter.ALTAF = alter.ALTAF.apply(lambda x :x.strip('万元').split('.')[0] if ~pd.isnull(x) else x)\n",
    "\n",
    "# 变动年 和 月\n",
    "alter['alter_year'] = alter.ALTDATE.apply(lambda x : x.split('-')[0]).astype('int')\n",
    "alter['alter_month'] = alter.ALTDATE.apply(lambda x : x.split('-')[1]).astype('int')\n",
    "# 是否最近一年(2015年)变动\n",
    "alter['alter_1year'] = np.where(alter.alter_year>2014,1,0)\n",
    "# 企业每个类型的变化次数（所有年份）\n",
    "alter_num = alter.groupby(['EID','ALTERNO'],as_index=False).agg({'ALTDATE':'count'}).pivot('EID','ALTERNO','ALTDATE').fillna(0)\n",
    "alter_num['alter_total'] = alter_num.apply(lambda x :x.sum(),axis=1)\n",
    "# 企业每个类型的变化次数（近一年）\n",
    "alter_num1 = alter.groupby(['EID','ALTERNO'],as_index=False).agg({'alter_1year':'sum'}).pivot('EID','ALTERNO','alter_1year').fillna(0)\n",
    "alter_num1['alter_1total'] = alter_num1.apply(lambda x :x.sum(),axis=1)\n",
    "alter_num1.rename(columns=lambda x :'1year'+str(x),inplace=True)\n",
    "# 变动年份的权重，越近，权重越大\n",
    "alter['alter_year_impor'] = (1/(1+alter['alter_year']-alter['alter_year'].min())).fillna(0)\n",
    "alter_year_impor = alter.groupby('EID').agg({'alter_year_impor':'mean'})\n",
    "\n",
    "\n",
    "# 增加新特征，资本变动差\n",
    "alter.ALTBE[alter.ALTBE == 'null'] = np.nan\n",
    "alter.ALTAF = alter.ALTAF.apply(lambda x : float(x) if ~pd.isnull(x) else x)\n",
    "alter.ALTBE = alter.ALTBE.apply(lambda x : float(x) if ~pd.isnull(x) else x)\n",
    "alter['alter_zczb'] = (alter.ALTAF - alter.ALTBE)\n",
    "\n",
    "# 只挑选变动为5的企业，并且最近变动的，去重,\n",
    "alter_5 = alter.loc[alter.ALTERNO == '05'][['EID','alter_zczb','alter_year','alter_month']]\n",
    "alter_5.sort_values(by=['alter_year','alter_month'],inplace=True,ascending=False)\n",
    "alter_5.drop_duplicates('EID',inplace=True)\n",
    "del(alter_5['alter_month'])\n",
    "# del(alter_5['alter_year'])\n",
    "# 还有挑选出企业的原始资本\n",
    "alter_ori_zb = alter.loc[alter.ALTERNO == '05'][['EID','ALTBE','alter_year','alter_month']]\n",
    "alter_ori_zb.sort_values(by=['alter_year','alter_month'],ascending=True,inplace=True)\n",
    "alter_ori_zb.drop_duplicates('EID',inplace=True)\n",
    "alter_ori_zb.rename(columns={'ALTBE':'ori_zb'},inplace=True)\n",
    "del(alter_ori_zb['alter_year'])\n",
    "del(alter_ori_zb['alter_month'])\n",
    "\n",
    "# 合并\n",
    "# 资本变动的表\n",
    "data = pd.merge(left=data,right=alter_5,left_on='EID',right_on='EID',how='left')\n",
    "data['alter_zczb'].fillna(0,inplace=True)\n",
    "data['alter_year'].fillna(2016,inplace=True)\n",
    "# 变动数量的表\n",
    "data = pd.merge(left=data,right=alter_num,left_on='EID',right_index=True,how='left').fillna(0)\n",
    "# 近一年变动\n",
    "data = pd.merge(left=data,right=alter_num1,left_on='EID',right_index=True,how='left').fillna(0)\n",
    "# 判断是否有变动的特征\n",
    "data['alter_if'] = np.where(data.alter_total>0,1,0)\n",
    "# 变动年份的重要性\n",
    "data = pd.merge(left=data,right=alter_year_impor,left_on='EID',right_index=True,how='left').fillna(0)\n",
    "# 原始资本表\n",
    "data = pd.merge(left=data,right=alter_ori_zb,left_on='EID',right_on='EID',how='left')\n",
    "data['ori_zb'].fillna(data['ZCZB'],inplace=True)\n",
    "\n",
    "# 去重，此表无缺失值\n",
    "branch.drop_duplicates(inplace=True)\n",
    "# 子机构成立和倒闭年份的重要性\n",
    "branch['bra_end_year_imp'] = (1/(branch.B_REYEAR.max() - branch.B_REYEAR+1)).fillna(0)\n",
    "branch['bra_reyear_impor'] = (1/(branch.B_REYEAR.max() - branch.B_REYEAR+1)).fillna(0)\n",
    "\n",
    "# 计算子机构个数和停业比例\n",
    "branch_num = branch.groupby(['EID','IFHOME'],as_index=False).agg({'TYPECODE':'count'}).pivot('EID','IFHOME','TYPECODE').fillna(0)\n",
    "branch_num['bra_total'] = branch_num.apply(lambda x: x.sum(),axis=1)\n",
    "branch_end = branch.groupby(['EID','IFHOME'],as_index=False).agg({'B_ENDYEAR':'count'}).pivot('EID','IFHOME','B_ENDYEAR').fillna(0)\n",
    "branch_end['bra_end_total'] = branch_end.apply(lambda x: x.sum(),axis=1)\n",
    "branch_data = pd.merge(branch_num,branch_end,left_index=True,right_index=True,how='left')\n",
    "branch_data['bra_pro'] = np.where(branch_data['bra_total']>0,branch_data['bra_end_total']/branch_data['bra_total'],0)\n",
    "## 近一年\n",
    "# 是否最近两年(2015年)成立或倒闭\n",
    "branch['1year_be'] = np.where(branch.B_REYEAR>2013,1,0)\n",
    "branch['1year_end'] = np.where(branch.B_ENDYEAR>2013,1,0)\n",
    "branch_num1 = branch.groupby(['EID','IFHOME'],as_index=False).agg({'1year_be':'sum'}).pivot('EID','IFHOME','1year_be').fillna(0)\n",
    "branch_num1['bra_1total'] = branch_num1.apply(lambda x: x.sum(),axis=1)\n",
    "branch_end1 = branch.groupby(['EID','IFHOME'],as_index=False).agg({'1year_end':'sum'}).pivot('EID','IFHOME','1year_end').fillna(0)\n",
    "branch_end1['bra_end_1total'] = branch_end1.apply(lambda x: x.sum(),axis=1)\n",
    "branch_data1 = pd.merge(branch_num1,branch_end1,left_index=True,right_index=True,how='left')\n",
    "branch_data1['bra_pro1'] = np.where(branch_data1['bra_1total']>0,branch_data1['bra_end_1total']/branch_data1['bra_1total'],0)\n",
    "\n",
    "# 子机构成立的平均年份和关停的平均年份\n",
    "# 填充缺失值\n",
    "# branch['B_ENDYEAR'].fillna(2015,inplace=True)\n",
    "# 机构年龄影响\n",
    "branch['branch_old'] = branch.B_ENDYEAR - branch.B_REYEAR\n",
    "bran_avg_year = branch.groupby('EID').agg({'bra_end_year_imp':'sum','bra_reyear_impor':'sum','B_ENDYEAR':'mean','branch_old':'mean'})\n",
    "\n",
    "# bran_avg_year = np.rint(bran_avg_year)\n",
    "# 子机构关停与成立时间差，越小说明\n",
    "# bran_avg_year['branch_old'] = (bran_avg_year.B_ENDYEAR - bran_avg_year.B_REYEAR).fillna(0)\n",
    "branch_data = pd.merge(left=branch_data,right=bran_avg_year,left_index=True,right_index=True,how='left')\n",
    "# 合并\n",
    "branch_data.duplicated()\n",
    "data = pd.merge(left=data,right=branch_data,left_on='EID',right_index=True,how='left',sort=False).fillna(0)\n",
    "data = pd.merge(left=data,right=branch_data1,left_on='EID',right_index=True,how='left',sort=False).fillna(0)\n",
    "\n",
    "data['branh_if']= np.where(data.EID.isin(branch.EID),1,0)\n",
    "\n",
    "right['right_code']=right['TYPECODE'].apply(lambda x:x[:3] if x[0].isalpha() else  'digit')\n",
    "right['ask_year'] = right['ASKDATE'].apply(lambda x :x.split('-')[0]).astype('int')\n",
    "## 近一年\n",
    "# 是否最近两年(2014年)申请\n",
    "right['ask_2year'] = np.where(right.ask_year>2013,1,0)\n",
    "\n",
    "# 年份的重要性\n",
    "right['right_year_imp'] = 1/(1+right.ask_year.max() - right.ask_year.min())\n",
    "# 权利类型的数量\n",
    "right_num = right.groupby(['EID','RIGHTTYPE'],as_index=False).agg({'TYPECODE':'count'}).pivot('EID','RIGHTTYPE','TYPECODE').fillna(0)\n",
    "right_num['total'] = right_num.apply(lambda x :x.sum(),axis=1)\n",
    "# right_num.rename(columns={'11':'right_11','12':'right_12','20':'right_20','30':'right_30','40':'right_40','50':'right_50',\n",
    "#                           '60':'right_60'},inplace=True)\n",
    "right_num.rename(columns=lambda x : 'right_'+str(x))\n",
    "# 近两年权利类型\n",
    "right_num2 = right.groupby(['EID','RIGHTTYPE'],as_index=False).agg({'ask_2year':'sum'}).pivot('EID','RIGHTTYPE','ask_2year').fillna(0)\n",
    "right_num2['total2'] = right_num2.apply(lambda x :x.sum(),axis=1)\n",
    "right_num2.rename(columns=lambda x : 'right_'+str(x))\n",
    "# 权利编码的数量\n",
    "right_code = pd.crosstab(right.EID,right.right_code)\n",
    "right_num['right_code_total'] = right_num.apply(lambda x :x.sum(),axis=1)\n",
    "\n",
    "# 年份求平均\n",
    "right_year = right.groupby('EID').agg({'right_year_imp':'mean'})\n",
    "# 合并\n",
    "data = pd.merge(left=data,right=right_num,left_on='EID',right_index=True,how='left')\n",
    "data = pd.merge(left=data,right=right_num2,left_on='EID',right_index=True,how='left')\n",
    "data = pd.merge(left=data,right=right_code,left_on='EID',right_index=True,how='left')\n",
    "data = pd.merge(left=data,right=right_year,left_on='EID',right_index=True,how='left')\n",
    "data.fillna(0,inplace=True)\n",
    "data.head()\n",
    "\n",
    "# 项目\n",
    "project.drop_duplicates(inplace=True)\n",
    "project['pro_year'] = project['DJDATE'].apply(lambda x :x.split('-')[0]).astype('int')\n",
    "project['pro_1year'] = np.where(project['pro_year']>2014,1,0)\n",
    "project['pro_year_imp'] = 1/(2016 - project['pro_year'])\n",
    "project_data = project.groupby('EID').agg({'TYPECODE':'count','pro_year_imp':'sum','pro_1year':'sum'}).rename(columns={\"TYPECODE\":'pro_num',})\n",
    "data = pd.merge(left=data,right=project_data,left_on='EID',right_index=True,how='left')\n",
    "data.fillna(0,inplace=True)\n",
    "# 法律\n",
    "lawsuit.drop_duplicates(inplace=True)\n",
    "lawsuit['law_year']=lawsuit['LAWDATE'].apply(lambda x :x.split('-')[0]).astype('int')\n",
    "lawsuit['law_1year'] = np.where(lawsuit['law_year']>2014,1,0)\n",
    "lawsuit['law_year_imp'] = 1/(2016 - lawsuit['law_year'])\n",
    "lawsuit['law_wei'] =  lawsuit['LAWAMOUNT']*lawsuit['law_year_imp']\n",
    "\n",
    "lawsuit_data = lawsuit.groupby('EID').agg({'TYPECODE':'count','LAWAMOUNT':'sum','law_year_imp':'sum','law_1year':'sum',\n",
    "                                           'law_wei':'sum'}).rename(columns={\"TYPECODE\":'law_num'})\n",
    "# lawsuit_data.head()\n",
    "data = pd.merge(left=data,right=lawsuit_data,left_on='EID',right_index=True,how='left')\n",
    "data.fillna(0,inplace=True)\n",
    "\n",
    "# 招聘\n",
    "recruit.drop_duplicates(inplace=True)\n",
    "recruit['re_year'] = recruit['RECDATE'].apply(lambda x :x.split('-')[0]).astype('int')\n",
    "recruit['re_month'] = recruit['RECDATE'].apply(lambda x :x.split('-')[1]).astype('int')\n",
    "recruit['re_month'] = np.where(recruit.re_year == 2014,recruit.re_month-12,recruit.re_month)\n",
    "recruit_num = recruit.groupby(['EID','WZCODE'],as_index=False).agg({'RECRNUM':'sum'}).pivot('EID','WZCODE','RECRNUM').fillna(0)\n",
    "recruit_num['recruit_tot'] = recruit_num.apply(lambda x :x.sum(),axis=1)\n",
    "recruit_month = recruit.groupby('EID')['re_month'].max()\n",
    "# 合并\n",
    "data = pd.merge(left=data,right=recruit_num,left_on='EID',right_index=True,how='left').fillna(0)\n",
    "data = pd.merge(left=data,right=pd.DataFrame(recruit_month),left_on='EID',right_index=True,how='left').fillna(-5)\n",
    "data.head()\n",
    "\n",
    "# 基本信息\n",
    "invest_data = pd.merge(invest,entbase,left_on='BTEID',right_on='EID',how='left')\n",
    "del(invest_data['EID_y'])\n",
    "del(invest_data['RGYEAR'])\n",
    "# 合并\n",
    "# 资本变动的表\n",
    "invest_data = pd.merge(left=invest_data,right=alter_5,left_on='BTEID',right_on='EID',how='left')\n",
    "del(invest_data['EID'])\n",
    "\n",
    "# 变动数量的表\n",
    "invest_data = pd.merge(left=invest_data,right=alter_num,left_on='BTEID',right_index=True,how='left').fillna(0)\n",
    "invest_data = pd.merge(left=invest_data,right=alter_num1,left_on='BTEID',right_index=True,how='left').fillna(0)\n",
    "# 原始资本表\n",
    "invest_data = pd.merge(left=invest_data,right=alter_ori_zb,left_on='BTEID',right_on='EID',how='left')\n",
    "del(invest_data['EID'])\n",
    "\n",
    "# 分支机构\n",
    "invest_data = pd.merge(left=invest_data,right=branch_data,left_on='BTEID',right_index=True,how='left',sort=False)\n",
    "invest_data = pd.merge(left=invest_data,right=branch_data1,left_on='BTEID',right_index=True,how='left',sort=False)\n",
    "\n",
    "# 权利\n",
    "invest_data = pd.merge(left=invest_data,right=right_num,left_on='BTEID',right_index=True,how='left')\n",
    "invest_data = pd.merge(left=invest_data,right=right_code,left_on='BTEID',right_index=True,how='left')\n",
    "invest_data = pd.merge(left=invest_data,right=right_year,left_on='BTEID',right_index=True,how='left')\n",
    "invest_data = pd.merge(left=invest_data,right=right_num2,left_on='BTEID',right_index=True,how='left')\n",
    "\n",
    "# 项目\n",
    "invest_data = pd.merge(left=invest_data,right=project_data,left_on='BTEID',right_index=True,how='left')\n",
    "\n",
    "# 招聘\n",
    "invest_data = pd.merge(left=invest_data,right=recruit_num,left_on='BTEID',right_index=True,how='left').fillna(0)\n",
    "invest_data = pd.merge(left=invest_data,right=pd.DataFrame(recruit_month),left_on='BTEID',right_index=True,how='left').fillna(-5)\n",
    "\n",
    "# 注册资本比例\n",
    "invest_data['ZCZB'].fillna(entbase.EID.mean(),inplace=True)\n",
    "invest_data['ZB_pro'] = invest_data.BTBL * invest_data.ZCZB\n",
    "# 亏损的钱\n",
    "invest_data['dificit'] = np.where(invest_data.BTENDYEAR.isnull(),0,invest_data['ZB_pro'])\n",
    "# 是否倒闭\n",
    "invest_data['end_if'] = np.where(invest_data.BTENDYEAR.isnull(),0,1)\n",
    "# 倒闭年限的权重，取倒数\n",
    "invest_data['endyear_impor'] = (1/(2016 - invest_data.BTENDYEAR)).fillna(0)\n",
    "# 亏损的钱的加权\n",
    "invest_data['dif_wei'] = invest_data['dificit'] * invest_data['endyear_impor']\n",
    "invest_data.fillna(0,inplace=True)\n",
    "invest_num = invest_data.groupby('EID_x').agg({'BTEID':'count'})\n",
    "invest_data = invest_data.drop(labels=['BTEID'],axis=1)\n",
    "invest_data = invest_data.groupby('EID_x').sum()\n",
    "invest_data.rename(columns=lambda x:'inv_'+str(x),inplace=True)\n",
    "data = pd.merge(data,invest_data,left_on='EID',right_index=True,how='left')\n",
    "data = pd.merge(data,invest_num,left_on='EID',right_index=True,how='left')\n",
    "data.fillna(0,inplace=True)\n",
    "data['invest_if'] = np.where(data.EID.isin(invest.EID),1,0)\n",
    "data.head()\n",
    "\n",
    "# 保存训练集\n",
    "train = pd.read_csv('train.csv',encoding='gb2312')\n",
    "data = pd.merge(left=data,right=train,left_on='EID',right_on='EID',how='left')\n",
    "data['TARGET'].fillna(-1,inplace=True)\n",
    "data_train = data[data['TARGET']!=-1]\n",
    "data_train.to_csv('data_train.csv',index=False)\n",
    "\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
